'''
操作数据库的方法
'''
import pymysql


def connect(db):
    '''
    连接数据库
    :param db:字典格式的，{"host":"192.168.2.36", "port":"3306", "name":"apple", "user":"root", "pwd":"123456"}
    :return:
    '''
    host = db['host']
    port = int(db['port'])
    name = db['name']
    user = db['user']
    pwd = db['pwd']
    try:
        conn = pymysql.connect(user=user,
                        password=pwd,
                        host=host,
                        database=name,
                        port=port,
                        charset="utf8")
        print('连接数据库成功。')
        return conn
    except Exception as e:
        print('连接数据库失败，异常信息为：',e)

def disconnect(conn):
    '''
    断开数据库连接
    :param conn:
    :return:
    '''
    try:
        conn.close()
    except Exception as e:
        print('断开数据库连接失败，异常信息为：',e)

def execute(conn,sql):
    '''
    执行sql语句
    :param conn:
    :param sql:
    :return:
    '''
    try:
        cursor = conn.cursor()   #获取游标
        cursor.execute(sql)   #执行sql语句
        conn.commit()  #提交
        cursor.close()  #关闭游标
        print(f'执行{sql}语句成功')
    except Exception as e:
        print('执行sql语句异常，异常信息为：',e)
# 为了脚本调用简单，封装一个方法
# 屏蔽了sql语句
def delete_user(db,phone):
    '''
    根据手机号码删除用户
    :param db:
    :param phone:
    :return:
    '''
    conn = connect(db)
    execute(conn,f'delete from member where MobilePhone={phone}')
    disconnect(conn)

# 测试代码，用完删除
if __name__ == '__main__':
    db = {"host":"192.168.2.36", "port":"3306", "name":"apple", "user":"root","pwd":"123456"}
    conn = connect(db)
    execute(conn,'delete from member where MobilePhone=15129299705')
    execute(conn,'delete from member where MobilePhone=17868877790')
    disconnect(conn)